from django.shortcuts import HttpResponseRedirect, render_to_response, HttpResponse
from django.template import RequestContext
from django.contrib import messages

from models import *
from functions import RetrieveGrades, CopyGroups, GenerateGradingSectionForms, GenerateGradingSectionGradeForms, GenerateAssignmentWeightFormset, GenerateGroupFormset
from apps.course.models import Course
from apps.course.functions import course_reverse, remove_course
from apps.management import Management
from apps.participants.models import CourseParticipant
from tags.templatetags.natural_sort import natural_sort

import logging
import xlwt
import datetime
import random

logger = logging.getLogger('pet.apps.gradebook.manage')

# Create your views here.
def manage(request, TheCourseParticipant, TheCourse, filter_category=None, filter_username=None, filter_assignment=None, options=None):
    TheUser = None
    if filter_username: TheUser = User.find_or_404(filter_username)
    
    if not (request.user.is_superuser or TheCourseParticipant.is_instructor() or TheCourseParticipant.is_assistant()):
        logger.info("User %s is not an instructor or assistant. Access denied.",TheCourseParticipant)
        messages.info(request,"Access denied")
        return HttpResponseRedirect(course_reverse('course_index',TheCourse))
    
    
    AnonymousGrading = TheCourse.anonymous_grading_default
    Categories, Grades, Metrics = RetrieveGrades(TheCourse, filter_category, filter_assignment)

    TargetCategory = TargetAssignment = None
    if filter_category and Categories:
        TargetCategory = Categories.items()[0][1]['info']
    if filter_assignment and Categories:
        temp_categories = Categories.items()[0][1].items()
        TargetAssignment = temp_categories[0][1]['info'] if temp_categories[0][0] != 'info' else temp_categories[1][1]['info']
        AnonymousGrading = AnonymousGrading and TargetAssignment['object'].allow_anonymous_grading()
    CategoryIndex = []
    GradingForms = {}
    UserIndex = []
    UploadFiles = False
    GroupIndex = []
    if Categories and Grades:
         #Check if we have any Assignments we need to display forms for
        GradingChanged = False
        Error = False
        ExcusedUser = None
        if request.method == "POST":
            if "AnonymousGrading" in request.POST:
                AnonymousGrading = request.POST['AnonymousGrading'] == 'True'
            if "AnonymousGradingToggle" in request.POST:
                AnonymousGrading = not AnonymousGrading
            GradingForms = GenerateGradingSectionGradeForms(TargetAssignment['object'] if TargetAssignment else None, Grades.keys(), request.POST)
            if "excuse" in request.POST:
                id = int(request.POST["excuse"])
                if not id: logger.error("Non-integer id %s used to excuse student from assignment %s",request.POST["excuse"],TargetAssignment)
                else:
                    ExcusedUser = User.objects.get(pk=id)
                    if not ExcusedUser or ExcusedUser not in GradingForms: logger.error("Invalid user is %s used to excuse student from assignment %s",id,TargetAssignment)
                                        
            for user, sections in GradingForms.items():
                if sections:
                    for section, form in sections.items():
                        if form.is_valid():
                            if form.has_changed():
                                GradingChanged = True
                                form.save()
                            else: logger.form_unchanged("GradingForm unchanged")
                            if user == ExcusedUser:
                                form.instance.excused = True
                                form.instance.save()
                                GradingChanged = True
                        else: Error = True; logger.debug("GradingForm %s invalid",form)
            if GradingChanged:
                if not Error: GradingForms = GenerateGradingSectionGradeForms(TargetAssignment['object'] if TargetAssignment else None, Grades.keys())
                Categories, Grades, Metrics = RetrieveGrades(TheCourse, filter_category, filter_assignment)
        else:
            GradingForms = GenerateGradingSectionGradeForms(TargetAssignment['object'] if TargetAssignment else None, Grades.keys())
        
        #Add links to be able to edit assignments and category weights    
        for student, grades in Grades.items():
            UserIndex.append(student)
            Grades[student]['link'] = None
            if not TheUser:
                Grades[student]['link'] = "%s&%s&%s" % (TargetCategory['slug'] if TargetCategory else "",
                                                        student.username, TargetAssignment['slug'] if TargetAssignment else "")
        for category, assignments in Categories.items():
            Categories[category]['info']['link'] = "%s&%s&" % (Categories[category]['info']['slug'],TheUser.username if TheUser else "")
            for assignment,sections in assignments.items():
                if assignment == 'info': continue
                Categories[category][assignment]['info']['link'] = "%s&%s&%s" % (Categories[category]['info']['slug'],
                                                             TheUser.username if TheUser else "", sections['info']['slug'])

        if TheUser: Grades = {TheUser:Grades[TheUser]}; UserIndex = [TheUser]
        if not filter_category and not filter_assignment:
            CategoryIndex = [(x[0],x[1]['info']['number']) for x in Categories.iteritems() if x[0] != 'Total' and x[0] != 'info']
            CategoryIndex = [item[0] for item in sorted(CategoryIndex,key=lambda item: item[1])]
        else:
            CategoryIndex = Categories.keys()
        
        
        if options and options == "upload": UploadFiles = True; AnonymousGrading = False
        
        #Check if we need to order the users by group instead of alphabetically
        UserIndex = natural_sort(UserIndex,key=lambda item: "%s, %s" % (item.last_name, item.first_name))
        GroupDict = {}
        if TargetAssignment and TargetAssignment["object"] and TargetAssignment["object"].groups.count() > 0:
            for user in UserIndex:
                group = user.get_group_for_assignment(TargetAssignment["object"])
                if not group.group_name in GroupDict: GroupDict[group.group_name] = []
                GroupDict[group.group_name if group else None].append(user)
            #Sort GroupIndex as a list of tuples from GroupDict
            if AnonymousGrading:
                for group_name,group_list in GroupDict.items():
                    random.shuffle(group_list)
                    GroupIndex.append((group_name,group_list))
                random.shuffle(GroupIndex)
            else:
                GroupIndex = natural_sort([(group_name,group_list) for group_name,group_list in GroupDict.items()])
        elif AnonymousGrading and TargetAssignment: random.shuffle(UserIndex); GroupIndex = [(None,UserIndex)]
        else: GroupIndex = [(None,UserIndex)]
        
    return render_to_response('gradebook/manage.html',{'Grades':Grades,
                                                       'TargetUser':TheUser,
                                                       'UserIndex':UserIndex,
                                                       'GroupIndex':GroupIndex,
                                                       'GradingForms':GradingForms,
                                                       'Metrics':Metrics,
                                                       'Categories':Categories,
                                                       'CategoryIndex':CategoryIndex,
                                                       'TargetCategory':TargetCategory,
                                                       'TargetAssignment':TargetAssignment,
                                                       'UploadFiles':UploadFiles,
                                                       'AnonymousGrading':AnonymousGrading,
                                                       }
                              ,context_instance=RequestContext(request))
Management.register_interface('Grades','INSTRUCTOR;ASSISTANT','manage',manage)

def assign_groups(request, TheCourseParticipant, TheCourse, category, title):
    logger.fxn_entry("Assigning groups for assignment %s - %s",category,title)
    TheAssignment = None
    for interface in Gradeables.get_interfaces():
        TheAssignment = interface.find(course=TheCourse.course_id,category_slug=category,title_slug=title)
        if TheAssignment: break
    
    if not TheAssignment:
        logger.http404("Assignment with category %s and title %s not found",category,title)
        raise Http404
    
    if request.method == "POST":
        HasError = False
        GroupForms, GroupUsers, Ungrouped, Multigrouped = GenerateGroupFormset(TheCourse.course_id,TheAssignment,request.POST)
        CopyForm = GradingGroupCopyForm(TheCourse.course_id,TheAssignment,request.POST)
        for form in GroupForms:
            if form.is_valid():
                if form.has_changed():
                    form.instance.content_object = TheAssignment
                    form.save()
                else: logger.form_unchanged("GroupForm for %s unchanged" % form.instance.group_name)
            elif form.instance.id: HasError = True; logger.error("GroupForm for %s invalid - %s" % (form.instance.group_name,form))
        
        if "copy_groups" in request.POST and CopyForm.is_valid():
            if CopyForm.has_changed():
                if CopyForm.cleaned_data['assignment']:
                    AssignmentToCopy = CopyForm.cleaned_data['assignment'].split(';')
                    AssignmentToCopy = ContentType.objects.get_for_id(AssignmentToCopy[0]).model_class().objects.get(pk=AssignmentToCopy[1])
                    CopyGroups(TheAssignment,AssignmentToCopy,GroupUsers,Ungrouped)
                    messages.info(request,'Copied groups from %s - %s',AssignmentToCopy.category, AssignmentToCopy.title)
                    logger.fxn_exit('Copied groups from %s - %s, reloading to continue editing',AssignmentToCopy.category, AssignmentToCopy.title)
                    return HttpResponseRedirect(course_reverse('management',TheCourse.course_id,app='grades', command='groups', arguments="%s&%s" % (TheAssignment.category_slug,TheAssignment.title_slug)))

        elif not HasError and "create_random_groups" in request.POST and "id_random_groups" in request.POST:
            #Make sure id_random_groups is a number
            num_groups = int(request.POST['id_random_groups'])
            if num_groups > 0:
                #Recalculate the ungrouped
                GroupForms, GroupUsers, Ungrouped, Multigrouped = GenerateGroupFormset(TheCourse.course_id,TheAssignment,request.POST)
                if len(Ungrouped) < num_groups: num_groups = len(Ungrouped)
                random.shuffle(Ungrouped)
                users_per_group,leftovers = divmod(len(Ungrouped),num_groups)
                for index in range(num_groups):
                    NewGroup = GradingGroup(group_name="Random Group %s" % (index+1),content_object=TheAssignment)
                    NewGroup.save()
                    for user_index in range(users_per_group):
                        NewGroup.users.add(Ungrouped.pop(0))
                    if index < leftovers: NewGroup.users.add(Ungrouped.pop(0)); print "Added another"
                if leftovers > 0:
                    messages.info(request,"Made %s random groups of %s members (%s groups have %s members)" % (num_groups,users_per_group,leftovers,(users_per_group+1)))
                else:                    
                    messages.info(request,"Made %s random groups of %s members" % (num_groups,users_per_group))
                logger.fxn_exit('Made %s random groups, reloading to continue editing',num_groups)
                return HttpResponseRedirect(course_reverse('management',TheCourse.course_id,app='grades', command='groups', arguments="%s&%s" % (TheAssignment.category_slug,TheAssignment.title_slug)))
        elif "equalize_groups" in request.POST:
            num_groups = TheAssignment.groups.count()
            if num_groups > 0:
                #Recalculate the ungrouped
                GroupForms, GroupUsers, Ungrouped, Multigrouped = GenerateGroupFormset(TheCourse.course_id,TheAssignment,request.POST)
                random.shuffle(Ungrouped)
                users_per_group,leftovers = divmod(len(Ungrouped)+len(GroupUsers),num_groups)
                for index,group in enumerate(TheAssignment.groups.all()):
                    group_count = group.users.count()
                    for user_index in range(users_per_group - group_count):
                        group.users.add(Ungrouped.pop(0))
                    if index < leftovers: group.users.add(Ungrouped.pop(0));
                if leftovers > 0:
                    messages.info(request,"Equalized groups to %s members (%s groups have %s members)" % (users_per_group,leftovers,(users_per_group+1)))
                else:                    
                    messages.info(request,"Equalized groups to %s members" % (users_per_group))
                logger.fxn_exit('Made %s random groups, reloading to continue editing',num_groups)
                return HttpResponseRedirect(course_reverse('management',TheCourse.course_id,app='grades', command='groups', arguments="%s&%s" % (TheAssignment.category_slug,TheAssignment.title_slug)))
        elif "delete_all" in request.POST:
            logger.info("Deleting all groups")
            for group in TheAssignment.groups.all(): group.delete()
            messages.info(request,"All groups successfully deleted")
            logger.fxn_exit('Deleted all groups from assignment %s',TheAssignment)
            return HttpResponseRedirect(course_reverse('management',TheCourse.course_id,app='grades', command='groups', arguments="%s&%s" % (TheAssignment.category_slug,TheAssignment.title_slug)))

        elif "delete_group" in request.POST:
            group_id = int(request.POST["delete_group"])
            if group_id:
                logger.info("Deleting group %s" % group_id)
                GroupToDelete = TheAssignment.groups.get(pk=group_id)
                if GroupToDelete:
                    GroupToDelete.delete()
                    messages.info(request,"Group successfully deleted")
                    logger.fxn_exit('Deleted group %s from assignment %s',group_id,TheAssignment)
                    return HttpResponseRedirect(course_reverse('management',TheCourse.course_id,app='grades', command='groups', arguments="%s&%s" % (TheAssignment.category_slug,TheAssignment.title_slug)))
                else: logger.error("Assignment %s has no group with id %s",TheAssignment,group_id)
                
        
        if not HasError:
            if 'Save' in request.POST and request.POST['Save'] == 'SaveReturn':
                logger.fxn_exit('Returning to gradebook')
                return HttpResponseRedirect(course_reverse('management',TheCourse.course_id,app='grades',command='manage',arguments='%s&&%s'%(category,title)))
            if 'Save' in request.POST and request.POST['Save'] == 'SaveEdit':
                logger.fxn_exit('Redirecting to self to continue editing')
                return HttpResponseRedirect(course_reverse('management',TheCourse.course_id,app='grades', command='groups', arguments="%s&%s" % (TheAssignment.category_slug,TheAssignment.title_slug)))
    else:
        GroupForms, GroupUsers, Ungrouped, Multigrouped = GenerateGroupFormset(TheCourse.course_id,TheAssignment)
        CopyForm = GradingGroupCopyForm(TheCourse.course_id,TheAssignment)
    
    return render_to_response('gradebook/assign_groups.html',{'GroupForms':GroupForms,'CopyForm':CopyForm if CopyForm.num_assignments > 0 else None,'Ungrouped':Ungrouped,'Multigrouped':Multigrouped,'Assignment':TheAssignment},context_instance=RequestContext(request))
    
Management.register_interface('Grades','INSTRUCTOR;ASSISTANT','groups',assign_groups)

def delete(request, TheCourseParticipant, TheCourse, category, title):
    logger.debug("course=%s,category=%s,title=%s",TheCourse.course_id,category,title)
    TheAssignment = ExternalAssignment.find_or_404(TheCourse.course_id, category, title)
    if TheCourseParticipant.status != "INSTRUCTOR":
        logger.info("CourseParticipant %s not allowed to delete assignments",TheCourseParticipant)
        raise Http404
    
    if request.method == 'POST':
        TheAssignment.delete()
        messages.info(request,"Assignment successfully deleted")
        return HttpResponseRedirect(course_reverse('management',TheCourse.course_id,app='grades'))
        
    return render_to_response('gradebook/delete.html',{'TheAssignment':TheAssignment},context_instance=RequestContext(request))
Management.register_interface('Grades','INSTRUCTOR','delete',delete)
    
def edit_assignment_weights(request, TheCourseParticipant, TheCourse, category):
    AWForms = []
    if request.method == "POST":
        WeightsChanged = False
        Error = False
        AWForms = GenerateAssignmentWeightFormset(TheCourse.course_id,category,request.POST)
        
        for form in AWForms:
            if form.is_valid():
                if form.has_changed():
                    WeightsChanged = True
                    form.save()
                else: logger.form_unchanged("AssignmentWeightForm unchanged")
            else: Error = True; logger.debug("AssignmentWeightForm %s invalid",form)
        
        if not Error and 'Save' in request.POST and request.POST['Save'] == 'SaveReturn':
                return HttpResponseRedirect(course_reverse('management',TheCourse.course_id,app='grades',command='manage',arguments='%s&&'%category))
        if not Error and WeightsChanged:
            #Recreate the formset to include anything that may have been added/removed
            AWForms = GenerateAssignmentWeightFormset(TheCourse.course_id,category)
        
            if 'Save' in request.POST and request.POST['Save'] == 'SaveEdit':
                return HttpResponseRedirect(course_reverse('management',TheCourse.course_id,app='grades',command='assignment_weights',arguments=category))
    else:
        AWForms = GenerateAssignmentWeightFormset(TheCourse.course_id,category)
    return render_to_response('gradebook/edit_assignment_weights.html',{'AWForms':AWForms},context_instance=RequestContext(request))
Management.register_interface('Grades','ASSISTANT;INSTRUCTOR','assignment_weights',edit_assignment_weights)

def edit_weights(request, TheCourseParticipant, TheCourse):
    GCForms = []
    if request.method == "POST":
        CategoriesChanged = False
        Error = False
        for category in GradingCategory.find(TheCourse.course_id,keep_queryset=True):
            GCForm = GradingCategoryForm(request.POST,instance=category,prefix="category%s"%category.number)
            GCForms.append(GCForm);
        for form in GCForms:
            if form.is_valid():
                if form.has_changed():
                    CategoriesChanged = True
                    form.save()
                else: logger.form_unchanged("GradingCategoryForm unchanged")
            else: Error = True; logger.debug("GradingCategoryForm %s invalid",form)
        #Reorder sections since questions have changed
        if CategoriesChanged:
            logger.debug("Categories changed - reordering")
            index = 1
            for category in GradingCategory.find(TheCourse.course_id,keep_queryset=True).order_by('number'):
                category.number = index
                category.save()
                index += 1
        if not Error and 'Save' in request.POST and request.POST['Save'] == 'SaveReturn':
                return HttpResponseRedirect(course_reverse('management',TheCourse.course_id,app='grades'))
        if not Error and CategoriesChanged:
            #Recreate the formset to include anything that may have been added/removed
            for category in GradingCategory.find(TheCourse.course_id,keep_queryset=True):
                GCForm = GradingCategoryForm(request.POST,instance=category,prefix="category%s"%category.number)
                GCForms.append(GCForm);
        
            if 'Save' in request.POST and request.POST['Save'] == 'SaveEdit':
                return HttpResponseRedirect(course_reverse('management',TheCourse.course_id,app='grades', command='weights'))
            
    else:
        for category in GradingCategory.find(TheCourse.course_id,keep_queryset=True):
            GCForm = GradingCategoryForm(instance=category,prefix="category%s"%category.number)
            GCForms.append(GCForm);
    return render_to_response('gradebook/edit_weights.html',{'GCForms':GCForms},context_instance=RequestContext(request))
Management.register_interface('Grades','ASSISTANT;INSTRUCTOR','weights',edit_weights)

def edit(request, TheCourseParticipant, TheCourse, category=None, title=None):
    TheAssignment = None
    if category and title: TheAssignment = ExternalAssignment.find(TheCourse.course_id, category, title)
    
    Anchor = ""
    Error = False
    if request.method == 'POST':
        AssignmentChanged = False
        AssignmentCreated = False
        SectionsChanged = False
        Form = ExternalAssignmentForm(request.POST, instance=TheAssignment, course=TheCourse.course_id)
        SectionForms = GenerateGradingSectionForms(graded=TheAssignment,postinfo=request.POST)
        if Form.is_valid():
            if Form.has_changed():
                if not TheAssignment: AssignmentCreate = True
                AssignmentChanged = True
                TheAssignment = Form.save()
            else:
                logger.form_unchanged("ExternalAssignment Form unchanged")
        else:
            Error = True; logger.debug("ExternalAssignment Form invalid")
        
        for form in SectionForms:
            if form.is_valid():
                if form.has_changed() and TheAssignment:
                    form.instance.content_object = TheAssignment
                    SectionsChanged = True
                    form.save()
                else: logger.form_unchanged("SectionForm unchanged or assignment %s does not exist",TheAssignment)
            else: Error = True; logger.debug("SectionForm %s invalid",form)
        
        if 'DeleteSection' in request.POST:
            logger.debug("Deleting section %s",request.POST['DeleteSection'])
            if TheAssignment:
                if TheAssignment.sections.count() > 1:
                    SectionsChanged = True
                    TheSection = TheAssignment.sections.filter(section_number=request.POST['DeleteSection'])
                    if TheSection: TheSection[0].delete()
                else: Error = True; logger.debug("Cannot delete last section"); messages.error(request,"Unable to delete section - Assignments must have at least one grading section")

        #Reorder sections since questions have changed
        if SectionsChanged:
            logger.debug("Sections changed - reordering")
            index = 1
            for section in TheAssignment.sections.order_by('section_number'):
                section.section_number = index
                section.save()
                index += 1
        
        #Now check for changes/additions
        if 'NewSection' in request.POST:
            if TheAssignment:
                logger.debug("Adding new section")
                AssignmentChanged = True
                NewSection = GradingSection(content_object=TheAssignment)
                NewSection.save()
                Anchor = "section%s" % NewSection.section_number
            else:
                logger.debug("No new question - assignment is %s ",TheAssignment)
                    
        if AssignmentCreated:
            return HttpResponseRedirect(course_reverse('management',TheCourse.course_id,app='grades',command='edit',arguments="%s&%s"%(TheAssignment.category_slug,TheAssignment.title_slug)))
        if not Error and 'Save' in request.POST and request.POST['Save'] == 'SaveReturn':
                return HttpResponseRedirect(course_reverse('management',TheCourse.course_id,app='grades',command='manage',arguments="%s&&%s"%(TheAssignment.category_slug,TheAssignment.title_slug)))
        if not Error and TheAssignment and (AssignmentChanged or SectionsChanged):
            #Recreate the formset to include anything that may have been added/removed
            Form = ExternalAssignmentForm(instance=TheAssignment,course=TheCourse.course_id)
            SectionForms = GenerateGradingSectionForms(graded=TheAssignment)
        
            if 'Save' in request.POST and request.POST['Save'] == 'SaveEdit':
                return HttpResponseRedirect(course_reverse('management',TheCourse.course_id,app='grades', command='edit', arguments="%s&%s" % (TheAssignment.category_slug,TheAssignment.title_slug)))
            
    else:
        initial_dict = {}
        if category and not title: initial_dict = {'category':category}
        Form = ExternalAssignmentForm(instance=TheAssignment,course=TheCourse.course_id,initial=initial_dict)
        SectionForms = GenerateGradingSectionForms(graded=TheAssignment)
        
    if Anchor:
        arguments = ""
        if category and title: arguments = "%s&%s" % (TheAssignment.category_slug, TheAssignment.title_slug)
        elif category: arguments = TheAssignment.category_slug
        return HttpResponseRedirect(course_reverse('management',TheCourse.course_id,app='grades',command='edit', arguments=arguments) + '#' + Anchor)
        
    return render_to_response('gradebook/edit.html',{'Form':Form,'SectionForms':SectionForms,'TheAssignment':TheAssignment},context_instance=RequestContext(request))
Management.register_interface('Grades','ASSISTANT;INSTRUCTOR','edit',edit)

def save_backup(request, TheCourseParticipant, TheCourse):
    pass
def load_backup(request, TheCourseParticipant, TheCourse):
    pass

def _set_border(dict,left=None,right=None,top=None,bottom=None):
    if not dict['style']:
        dict['style'] = xlwt.XFStyle()
        
    if left: dict['style'].borders.left = left
    if right: dict['style'].borders.right = right
    if top: dict['style'].borders.top = top
    if bottom: dict['style'].borders.bottom = bottom
def _set_format_str(dict,format_str):
    if not dict['style']:
        dict['style'] = xlwt.XFStyle()
        
    dict['style'].num_format_str = format_str
    
def _convert_to_index(row,col):
    alpha = 'abcdefghijklmnopqrstuvwxyz'
    col_list = []
    q,r = divmod(col,len(alpha))
    while q > 0:
        col_list.insert(0,alpha[r])
        q,r = divmod(col,len(alpha))    
    col_list.insert(0,alpha[r])
    return ''.join(col_list) + str(row+1)

class AutoVivification(dict):
    """Implementation of perl's autovivification feature by nokslo (http://stackoverflow.com/users/17160/nosklo)"""
    def __getitem__(self, item):
        try:
            return dict.__getitem__(self, item)
        except KeyError:
            value = self[item] = type(self)()
            return value
def download_excel(request, TheCourseParticipant, TheCourse):
    #Set up Workbook & Worksheets
    Categories, Grades, Metrics = RetrieveGrades(TheCourse)
    UserList = sorted(Grades.keys(),key=lambda item: "%s, %s" % (item.last_name, item.first_name))

    Worksheets = AutoVivification()
    TotalList = []
    for index,user in enumerate(UserList):
        Worksheets['Overall'][index+4][0]['label'] = user.last_name
        Worksheets['Overall'][index+4][1]['label'] = user.first_name
        TotalList.append([])
    Worksheets['Overall'][index+5][1]['label'] = 'Min'
    Worksheets['Overall'][index+6][1]['label'] = 'Max'
    Worksheets['Overall'][index+7][1]['label'] = 'Average'
    Worksheets['Overall'][index+8][1]['label'] = 'Median'
    for cat_index, (category, assignments) in enumerate(Categories.items()):
        Worksheets['Overall'][2][cat_index+2]['label'] = category
        Worksheets['Overall'][3][cat_index+2]['label'] = assignments['info']['weight']
        _set_border(Worksheets['Overall'][2][cat_index+2],left=xlwt.Borders.THICK, right=xlwt.Borders.THICK, top=xlwt.Borders.THICK)
        _set_border(Worksheets['Overall'][3][cat_index+2],left=xlwt.Borders.THICK, right=xlwt.Borders.THICK, bottom=xlwt.Borders.THICK)
        for index,user in enumerate(UserList):
            Worksheets[category][index+4][0]['label'] = user.last_name
            Worksheets[category][index+4][1]['label'] = user.first_name
            TotalList[index].append("%s*%s"%(_convert_to_index(index+4,cat_index+2),_convert_to_index(3,cat_index+2)))
        Worksheets[category][index+5][1]['label'] = 'Min'
        Worksheets[category][index+6][1]['label'] = 'Max'
        Worksheets[category][index+7][1]['label'] = 'Average'
        Worksheets[category][index+8][1]['label'] = 'Median'
        index = 2 #Start at the column after first_name
        section_total = 0
        for assignment, sections in assignments.items():
            if assignment == 'info': continue
            Worksheets[category][1][index]['label'] = assignment.title
            _set_border(Worksheets[category][1][index],left=xlwt.Borders.THICK)
            _set_border(Worksheets[category][2][index],left=xlwt.Borders.THICK)
            _set_border(Worksheets[category][3][index],left=xlwt.Borders.THICK)
            for row, user in enumerate(UserList):
                    _set_border(Worksheets[category][row+4][index],left=xlwt.Borders.THICK)
            for section, section_info in sections.items():
                if section == 'info': continue
                section_total += 1
                _set_border(Worksheets[category][1][index],top=xlwt.Borders.THICK)
                if section.title != assignment.title: Worksheets[category][2][index]['label'] = section.title
                Worksheets[category][3][index]['label'] = section.total_points
                _set_border(Worksheets[category][3][index],bottom=xlwt.Borders.THICK)
                for row,user in enumerate(UserList):
                    Worksheets[category][row+4][index]['label'] = Grades[user][category][assignment][section]['points']
                Worksheets[category][row+5][index]['label'] = xlwt.Formula('MIN(%s:%s)'%(_convert_to_index(row+5-len(UserList),index),_convert_to_index(row+4,index)))
                Worksheets[category][row+6][index]['label'] = xlwt.Formula('MAX(%s:%s)'%(_convert_to_index(row+5-len(UserList),index),_convert_to_index(row+4,index)))
                Worksheets[category][row+7][index]['label'] = xlwt.Formula('AVERAGE(%s:%s)'%(_convert_to_index(row+5-len(UserList),index),_convert_to_index(row+4,index)))
                Worksheets[category][row+8][index]['label'] = xlwt.Formula('MEDIAN(%s:%s)'%(_convert_to_index(row+5-len(UserList),index),_convert_to_index(row+4,index)))
                index += 1
                _set_border(Worksheets[category][row+4][index-1],bottom=xlwt.Borders.THICK)
                _set_border(Worksheets[category][row+4][index],bottom=xlwt.Borders.THICK)
            for row, user in enumerate(UserList):
                _set_border(Worksheets[category][row+4][index-1],right=xlwt.Borders.THICK)
               
            _set_border(Worksheets[category][1][index-1],right=xlwt.Borders.THICK)
            _set_border(Worksheets[category][2][index-1],right=xlwt.Borders.THICK)
            _set_border(Worksheets[category][3][index-1],right=xlwt.Borders.THICK)
        Worksheets[category][1][index]['label'] = 'Total'
        Worksheets[category][3][index]['label'] = xlwt.Formula('SUM(%s:%s)'%(_convert_to_index(3,2),_convert_to_index(3,index-1)))
        _set_border(Worksheets[category][1][index],top=xlwt.Borders.THICK, left=xlwt.Borders.THICK, right=xlwt.Borders.THICK)
        _set_border(Worksheets[category][2][index],left=xlwt.Borders.THICK, right=xlwt.Borders.THICK)
        _set_border(Worksheets[category][3][index],bottom=xlwt.Borders.THICK, left=xlwt.Borders.THICK, right=xlwt.Borders.THICK)
        for row,user in enumerate(UserList):
            Worksheets['Overall'][row+4][cat_index+2]['label'] = xlwt.Formula("'%s!'%s"%(category[:30],_convert_to_index(row+4,index)))
            _set_format_str(Worksheets['Overall'][row+4][cat_index+2],'0.00%')
            _set_border(Worksheets['Overall'][row+4][cat_index+2],left=xlwt.Borders.THICK,right=xlwt.Borders.THICK)
            Worksheets[category][row+4][index]['label'] = xlwt.Formula('SUM(%s:%s)/%s'%(_convert_to_index(row+4,2),_convert_to_index(row+4,2+section_total-1),_convert_to_index(3,index)))
            _set_format_str(Worksheets[category][row+4][index],'0.00%')
            _set_border(Worksheets[category][row+4][index],right=xlwt.Borders.THICK)
        Worksheets[category][row+5][index]['label'] = xlwt.Formula('MIN(%s:%s)'%(_convert_to_index(row+5-len(UserList),index),_convert_to_index(row+4,index)))
        _set_format_str(Worksheets[category][row+5][index],'0.00%')
        Worksheets[category][row+6][index]['label'] = xlwt.Formula('MAX(%s:%s)'%(_convert_to_index(row+5-len(UserList),index),_convert_to_index(row+4,index)))
        _set_format_str(Worksheets[category][row+6][index],'0.00%')
        Worksheets[category][row+7][index]['label'] = xlwt.Formula('AVERAGE(%s:%s)'%(_convert_to_index(row+5-len(UserList),index),_convert_to_index(row+4,index)))
        _set_format_str(Worksheets[category][row+7][index],'0.00%')
        Worksheets[category][row+8][index]['label'] = xlwt.Formula('MEDIAN(%s:%s)'%(_convert_to_index(row+5-len(UserList),index),_convert_to_index(row+4,index)))
        _set_format_str(Worksheets[category][row+8][index],'0.00%')
        
        Worksheets['Overall'][row+5][cat_index+2]['label'] = xlwt.Formula('MIN(%s:%s)'%(_convert_to_index(row+5-len(UserList),cat_index+2),_convert_to_index(row+4,cat_index+2)))
        _set_format_str(Worksheets['Overall'][row+5][cat_index+2],'0.00%')
        Worksheets['Overall'][row+6][cat_index+2]['label'] = xlwt.Formula('MAX(%s:%s)'%(_convert_to_index(row+5-len(UserList),cat_index+2),_convert_to_index(row+4,cat_index+2)))
        _set_format_str(Worksheets['Overall'][row+6][cat_index+2],'0.00%')
        Worksheets['Overall'][row+7][cat_index+2]['label'] = xlwt.Formula('AVERAGE(%s:%s)'%(_convert_to_index(row+5-len(UserList),cat_index+2),_convert_to_index(row+4,cat_index+2)))
        _set_format_str(Worksheets['Overall'][row+7][cat_index+2],'0.00%')
        Worksheets['Overall'][row+8][cat_index+2]['label'] = xlwt.Formula('MEDIAN(%s:%s)'%(_convert_to_index(row+5-len(UserList),cat_index+2),_convert_to_index(row+4,cat_index+2)))
        _set_format_str(Worksheets['Overall'][row+8][cat_index+2],'0.00%')
        _set_border(Worksheets['Overall'][row+4][cat_index+2],left=xlwt.Borders.THICK,right=xlwt.Borders.THICK,bottom=xlwt.Borders.THICK)
    Worksheets['Overall'][2][cat_index+3]['label'] = 'Total'
    _set_border(Worksheets['Overall'][2][cat_index+3],left=xlwt.Borders.THICK, right=xlwt.Borders.THICK, top=xlwt.Borders.THICK)
    Worksheets['Overall'][3][cat_index+3]['label'] = xlwt.Formula('SUM(%s:%s)'%(_convert_to_index(3,2),_convert_to_index(3,cat_index+2)))
    _set_border(Worksheets['Overall'][3][cat_index+3],left=xlwt.Borders.THICK, right=xlwt.Borders.THICK, bottom=xlwt.Borders.THICK)
    for row,user in enumerate(UserList):
        Worksheets['Overall'][row+4][cat_index+3]['label'] = xlwt.Formula('(%s)/%s'%(' + '.join(TotalList[row]),_convert_to_index(3,cat_index+3)))
        _set_format_str(Worksheets['Overall'][row+4][cat_index+3],'0.00%')
        _set_border(Worksheets['Overall'][row+4][cat_index+3],left=xlwt.Borders.THICK, right=xlwt.Borders.THICK)
    _set_border(Worksheets['Overall'][row+4][cat_index+3],bottom=xlwt.Borders.THICK)
    
    Worksheets['Overall'][row+5][cat_index+3]['label'] = xlwt.Formula('MIN(%s:%s)'%(_convert_to_index(row+5-len(UserList),cat_index+3),_convert_to_index(row+4,cat_index+3)))
    _set_format_str(Worksheets['Overall'][row+5][cat_index+3],'0.00%')
    Worksheets['Overall'][row+6][cat_index+3]['label'] = xlwt.Formula('MAX(%s:%s)'%(_convert_to_index(row+5-len(UserList),cat_index+3),_convert_to_index(row+4,cat_index+3)))
    _set_format_str(Worksheets['Overall'][row+6][cat_index+3],'0.00%')
    Worksheets['Overall'][row+7][cat_index+3]['label'] = xlwt.Formula('AVERAGE(%s:%s)'%(_convert_to_index(row+5-len(UserList),cat_index+3),_convert_to_index(row+4,cat_index+3)))
    _set_format_str(Worksheets['Overall'][row+7][cat_index+3],'0.00%')
    Worksheets['Overall'][row+8][cat_index+3]['label'] = xlwt.Formula('MEDIAN(%s:%s)'%(_convert_to_index(row+5-len(UserList),cat_index+3),_convert_to_index(row+4,cat_index+3)))
    _set_format_str(Worksheets['Overall'][row+8][cat_index+3],'0.00%')
        
    NewWorkbook = xlwt.Workbook()
    #Make all the worksheets
    CurrentWorksheet = NewWorkbook.add_sheet('Overall') # So this goes first
    for sheet,rows in Worksheets.items():
        if sheet=='Overall': CurrentWorksheet = NewWorkbook.get_sheet(0)
        else: CurrentWorksheet = NewWorkbook.add_sheet('%s' % sheet[:30])
    for index,sheet,rows in enumerate(Worksheets.items()):
        CurrentWorksheet = NewWorkbook.get_sheet(index)
        for row, cols in rows.items():
            for col, contents in cols.items():
                CurrentWorksheet.write(row,col,contents['label'] if 'label' in contents else "", contents['style'] if 'style' in contents else xlwt.Style.default_style)
    
    response = HttpResponse(mimetype="application/ms-excel")
    response['Content-Disposition'] = 'attachment; filename=Gradebook.%s.xls' % (datetime.datetime.now().strftime('%y.%m.%d.%H.%m'))
    NewWorkbook.save(response)
    return response
Management.register_interface('Grades','ASSISTANT;INSTRUCTOR','download',download_excel)